package cn.bdqn.daoImpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cn.bdqn.dao.BaseDao;
import cn.bdqn.dao.Deptdao;
import cn.bdqn.pojo.Dept;
import cn.bdqn.pojo.Page;

public class DeptdaoImpl extends BaseDao implements Deptdao {

	@Override
	public List<Dept> getDepts() {
		List<Dept> list = new ArrayList<Dept>();
		String sql = "select * from  dept";
		Object[] params = {};
		ResultSet rs = this.executeSQL(sql, params);
		try {
			while (rs.next()) {
				Dept dept = new Dept();
				dept.setDeptno(rs.getInt("deptno"));
				dept.setDname(rs.getString("dname"));
				dept.setLoc(rs.getString("loc"));
				list.add(dept);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return list;
	}

	@Override
	public Dept getDept(int deptno) {
		Dept dept = new Dept();
		String sql = "select * from dept where deptno=?";
		Object[] params = { deptno };
		ResultSet rs = this.executeSQL(sql, params);
		try {
			while (rs.next()) {

				dept.setDeptno(rs.getInt("deptno"));
				dept.setDname(rs.getString("dname"));
				dept.setLoc(rs.getString("loc"));

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return dept;
	}

	@Override
	public int getCounts() {
		int counts = 0;
		String sql="select count(*) from dept";
		Object [] params={};
		ResultSet rs=this.executeSQL(sql, params);
		try {
			while(rs.next()){
				counts=rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return counts;
	}

	@Override
	public List<Dept> getPageList(int pageIndex, int pageSize)  {
		List<Dept> list= new ArrayList<Dept>();
		String sql="select * from (select a.*,rownum rn from (select * from dept) a) where rn between ? and ?";
		Page page = new Page();
		page.setCounts(getCounts());
		page.setPageIndex(pageIndex);
		page.setPageSize(pageSize);
		int start =page.getStart();
		int end=page.getEnd();
		Object [] params={start,end};
		ResultSet rs = this.executeSQL(sql, params);
		try {
			while(rs.next()){
				int deptno=rs.getInt("deptno");
				String dname=rs.getString("dname");
				String loc=rs.getString("loc");
				Dept dept=new Dept(deptno, dname, loc);
				list.add(dept);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return list;
	}

	@Override
	public boolean delete(int deptno) {
		boolean flag=false;
		String sql="delete from dept where deptno=?";
		Object[] params={deptno};
		int updateRows=this.executeUpdate(sql, params);
		if(updateRows>0){
			System.out.println("删除成功");
			flag=true;
		}
		return flag;
	}

	@Override
	public boolean add(int deptno, String dname, String loc) {
		boolean flag=true;
		String sql=" insert into dept values(?,?,?)";
		Object [] params ={deptno,dname,loc};
		int updateRows=this.executeUpdate(sql, params);
		if(updateRows>0){
			System.out.println("删除成功");
			flag=true;
		}
		return flag;
	}

	@Override
	public boolean update(Dept dept) {
		boolean flag=false;
		String sql="update dept set dname=?,loc=? where deptno=?";
		Object []params={dept.getDname(),dept.getLoc(),dept.getDeptno()};
		int updateRows=this.executeUpdate(sql, params);
		if(updateRows>0){
			System.out.println("修改成功");
			flag=true;
		}
		return flag;
	}

}
